import 'package:sqflite/sqflite.dart' as sql;
import 'package:path/path.dart';
import '../models/transaction.dart';
import '../models/category.dart';

class DatabaseService {
  static final DatabaseService instance = DatabaseService._init();
  static sql.Database? _database;

  DatabaseService._init();

  Future<sql.Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDB('transactions.db');
    return _database!;
  }

  Future<sql.Database> _initDB(String filePath) async {
    final dbPath = await sql.getDatabasesPath();
    final path = join(dbPath, filePath);

    return await sql.openDatabase(
      path,
      version: 1,
      onCreate: _createDB,
    );
  }

  Future<void> _createDB(sql.Database db, int version) async {
    await db.execute('''
      CREATE TABLE transactions(
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        amount REAL NOT NULL,
        date TEXT NOT NULL,
        category TEXT NOT NULL,
        type TEXT NOT NULL,
        note TEXT
      )
    ''');

    await db.execute('''
      CREATE TABLE categories(
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        icon TEXT NOT NULL,
        type TEXT NOT NULL,
        is_default INTEGER NOT NULL DEFAULT 0,
        sort_order INTEGER NOT NULL
      )
    ''');

    await _insertDefaultCategories(db);
  }

  Future<void> _insertDefaultCategories(sql.Database db) async {
    final defaultCategories = [
      Category(
        id: 'expense_daily',
        name: '日常支出',
        icon: 'shopping_bag',
        type: TransactionType.expense,
        isDefault: true,
        sortOrder: 0,
      ),
      Category(
        id: 'expense_food',
        name: '餐饮',
        icon: 'restaurant',
        type: TransactionType.expense,
        isDefault: true,
        sortOrder: 1,
      ),
      Category(
        id: 'expense_transport',
        name: '交通',
        icon: 'directions_bus',
        type: TransactionType.expense,
        isDefault: true,
        sortOrder: 2,
      ),
      Category(
        id: 'expense_shopping',
        name: '购物',
        icon: 'shopping_cart',
        type: TransactionType.expense,
        isDefault: true,
        sortOrder: 3,
      ),
      Category(
        id: 'income_salary',
        name: '工资',
        icon: 'account_balance_wallet',
        type: TransactionType.income,
        isDefault: true,
        sortOrder: 0,
      ),
      Category(
        id: 'income_bonus',
        name: '奖金',
        icon: 'card_giftcard',
        type: TransactionType.income,
        isDefault: true,
        sortOrder: 1,
      ),
    ];

    for (final category in defaultCategories) {
      await db.insert('categories', category.toMap());
    }
  }

  /// 插入新交易记录
  Future<void> insertTransaction(Transaction transaction) async {
    final db = await database;
    await db.insert(
      'transactions',
      transaction.toMap(),
      conflictAlgorithm: sql.ConflictAlgorithm.replace,
    );
  }

  /// 获取所有交易记录
  Future<List<Transaction>> getAllTransactions() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'transactions',
      orderBy: 'date DESC',
    );

    return List.generate(maps.length, (i) {
      return Transaction(
        id: maps[i]['id'],
        title: maps[i]['title'],
        amount: maps[i]['amount'],
        date: DateTime.parse(maps[i]['date']),
        category: maps[i]['category'],
        type: TransactionType.values.firstWhere(
          (e) => e.name == maps[i]['type'],
        ),
        note: maps[i]['note'],
      );
    });
  }

  /// 删除交易记录
  Future<void> deleteTransaction(String id) async {
    final db = await database;
    await db.delete(
      'transactions',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// 获取指定月份的交易记录
  Future<List<Transaction>> getTransactionsByMonth(DateTime date) async {
    final db = await database;
    final startDate = DateTime(date.year, date.month, 1);
    final endDate = DateTime(date.year, date.month + 1, 0);

    final List<Map<String, dynamic>> maps = await db.query(
      'transactions',
      where: 'date BETWEEN ? AND ?',
      whereArgs: [
        startDate.toIso8601String(),
        endDate.toIso8601String(),
      ],
      orderBy: 'date DESC',
    );

    return List.generate(maps.length, (i) {
      return Transaction(
        id: maps[i]['id'],
        title: maps[i]['title'],
        amount: maps[i]['amount'],
        date: DateTime.parse(maps[i]['date']),
        category: maps[i]['category'],
        type: TransactionType.values.firstWhere(
          (e) => e.name == maps[i]['type'],
        ),
        note: maps[i]['note'],
      );
    });
  }

  /// 获取指定年份的交易记录
  Future<List<Transaction>> getTransactionsByYear(int year) async {
    final db = await database;
    final startDate = DateTime(year, 1, 1);
    final endDate = DateTime(year, 12, 31);

    final List<Map<String, dynamic>> maps = await db.query(
      'transactions',
      where: 'date BETWEEN ? AND ?',
      whereArgs: [
        startDate.toIso8601String(),
        endDate.toIso8601String(),
      ],
      orderBy: 'date DESC',
    );

    return List.generate(maps.length, (i) {
      return Transaction.fromMap(maps[i]);
    });
  }

  /// 获取指定年份的月度统计数据
  Future<Map<int, MonthlyStats>> getMonthlyStatsByYear(int year) async {
    final transactions = await getTransactionsByYear(year);
    final monthlyStats = <int, MonthlyStats>{};

    // 初始化每个月的数据
    for (var month = 1; month <= 12; month++) {
      monthlyStats[month] = MonthlyStats(income: 0, expense: 0);
    }

    // 统计每月数据
    for (final transaction in transactions) {
      final month = transaction.date.month;
      final stats = monthlyStats[month]!;
      
      if (transaction.type == TransactionType.income) {
        monthlyStats[month] = MonthlyStats(
          income: stats.income + transaction.amount,
          expense: stats.expense,
        );
      } else {
        monthlyStats[month] = MonthlyStats(
          income: stats.income,
          expense: stats.expense + transaction.amount,
        );
      }
    }

    return monthlyStats;
  }

  /// 获取所有分类
  Future<List<Category>> getAllCategories() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'categories',
      orderBy: 'type, sort_order',
    );

    return List.generate(maps.length, (i) => Category.fromMap(maps[i]));
  }

  /// 获取指定类型的分类
  Future<List<Category>> getCategoriesByType(TransactionType type) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'categories',
      where: 'type = ?',
      whereArgs: [type.name],
      orderBy: 'sort_order',
    );

    return List.generate(maps.length, (i) => Category.fromMap(maps[i]));
  }

  /// 添加分类
  Future<void> insertCategory(Category category) async {
    final db = await database;
    await db.insert(
      'categories',
      category.toMap(),
      conflictAlgorithm: sql.ConflictAlgorithm.replace,
    );
  }

  /// 更新分类
  Future<void> updateCategory(Category category) async {
    final db = await database;
    await db.update(
      'categories',
      category.toMap(),
      where: 'id = ?',
      whereArgs: [category.id],
    );
  }

  /// 删除分类
  Future<void> deleteCategory(String id) async {
    final db = await database;
    await db.delete(
      'categories',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// 更新分类排序
  Future<void> updateCategoriesOrder(List<Category> categories) async {
    final db = await database;
    final batch = db.batch();
    
    for (var i = 0; i < categories.length; i++) {
      final category = categories[i].copyWith(sortOrder: i);
      batch.update(
        'categories',
        {'sort_order': i},
        where: 'id = ?',
        whereArgs: [category.id],
      );
    }
    
    await batch.commit();
  }
}

/// 月度统计数据模型
class MonthlyStats {
  final double income;
  final double expense;

  MonthlyStats({required this.income, required this.expense});

  double get balance => income - expense;
} 